/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Servicios;

import java.sql.*;

/**
 *
 * @author pablo.montero
 */
public class ConexionDB {

    Connection con;
    CallableStatement cst;
    ResultSet r;

    public void CerrarConexion() {
        try {
            if (!con.isClosed()) {
                con.close();
            }
        } catch (Exception ex) {
            ex.getMessage().toString();
        }
    }

    public ConexionDB() {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //datos equivale al lo ultimo que corri con el odbc32
            con = DriverManager.getConnection("jdbc:odbc:datos");
        } catch (Exception e) {
            SM.lastError = e.getMessage();
        }
    }

    private void ReOpenConnection() {
        try {
            con = DriverManager.getConnection("jdbc:odbc:datos");
        } catch (Exception e) {
            SM.lastError = e.getMessage();
        }
    }

    ResultSet getUsua() {
        ResultSet resu = null;
        try {
            ReOpenConnection(); //lo uso para abrir la conexion
            cst = con.prepareCall("{ call dbo.selectUsua(?) }");
            cst.setString(1, "r");
            resu = cst.executeQuery();
        } catch (Exception ex) {
            SM.lastError = ex.getMessage();
        }
        return resu;

    }
    ResultSet getUsua(String id) {
        ResultSet resu = null;
        try {
            ReOpenConnection(); //lo uso para abrir la conexion
            cst = con.prepareCall("{ call dbo.selectUsuaP(?) }");
            cst.setString(1, id);
            resu = cst.executeQuery();

        } catch (Exception ex) {
            SM.lastError = ex.getMessage();
        }
        return resu;
    }
    boolean insertUsua(String id, String nom, String ape, String ape2) {

        boolean r = true;
        try {
            ReOpenConnection();
            cst = con.prepareCall("{call insertUsua(?,?,?,?)}");
            cst.setString(1, id);
            cst.setString(2, nom);
            cst.setString(3, ape);
            cst.setString(4, ape2);
            cst.executeUpdate();
        } catch (Exception ex) {
            r = false;
            SM.lastError = ex.getMessage();
        }
        return r;
    }
    boolean updateUsua(String id, String nom, String ape, String ape2) {
        boolean r = true;
        try {
            ReOpenConnection();
            cst = con.prepareCall("{call updateUsua(?,?,?,?)}");
            cst.setString(1, id);
            cst.setString(2, nom);
            cst.setString(3, ape);
            cst.setString(4, ape2);
            cst.executeUpdate();
        } catch (Exception ex) {
            r = false;
            SM.lastError = ex.getMessage();
        }
        return r;
    }
    boolean deleteUsua(String id) {
        boolean r = true;
        try {
            ReOpenConnection();
            cst = con.prepareCall("{call deleteUsua(?)}");
            cst.setString(1, id);
            cst.executeUpdate();
        } catch (Exception ex) {
            r = false;
            SM.lastError = ex.getMessage();
        }
        return r;
    }
        
    ResultSet getPeli() {
        ResultSet resu = null;
        try {
            ReOpenConnection(); //lo uso para abrir la conexion
            cst = con.prepareCall("{ call dbo.selectPeli(?) }");
            cst.setString(1, "r");
            resu = cst.executeQuery();

        } catch (Exception ex) {
            SM.lastError = ex.getMessage();
        }
        return resu;
    }
    ResultSet getPeli(String id) {
        ResultSet resu = null;
        try {
            ReOpenConnection(); //lo uso para abrir la conexion
            cst = con.prepareCall("{ call dbo.selectPeliP(?) }");
            cst.setString(1, id);
            resu = cst.executeQuery();

        } catch (Exception ex) {
            SM.lastError = ex.getMessage();
        }
        return resu;
    }
    boolean insertPeli(String id, String nom, String ano, int cantTotalPelis) {

        boolean r = true;
        try {
            ReOpenConnection();
            cst = con.prepareCall("{call insertPeli(?,?,?,?)}");
            cst.setString(1, id);
            cst.setString(2, nom);
            cst.setString(3, ano);
            cst.setInt(4, cantTotalPelis);
            cst.executeUpdate();
        } catch (Exception ex) {
            r = false;
            SM.lastError = ex.getMessage();
        }
        return r;
    }
    boolean updatePeli(String id, String nom, String ano, int cantTotalPelis) {
        boolean r = true;
        try {
            ReOpenConnection();
            cst = con.prepareCall("{call updatePeli(?,?,?,?)}");
            cst.setString(1, id);
            cst.setString(2, nom);
            cst.setString(3, ano);
            cst.setInt(4, cantTotalPelis);
            cst.executeUpdate();
        } catch (Exception ex) {
            r = false;
            SM.lastError = ex.getMessage();
        }
        return r;
    }
    boolean deletePeli(String id) {
        boolean r = true;
        try {
            ReOpenConnection();
            cst = con.prepareCall("{call deletePeli(?)}");
            cst.setString(1, id);
            cst.executeUpdate();
        } catch (Exception ex) {
            r = false;
            SM.lastError = ex.getMessage();
        }
        return r;
    }

    ResultSet getPeliAlqui(String id_usua) {
        ResultSet resu = null;
        try {
            ReOpenConnection(); //lo uso para abrir la conexion
            cst = con.prepareCall("{ call dbo.selectPeliAlquiladasPU(?) }");
            cst.setString(1, id_usua);
            resu = cst.executeQuery();

        } catch (Exception ex) {
            SM.lastError = ex.getMessage();
        }
        return resu;
    }
    ResultSet getPeliAlqui(String id_Peli,int i) {
        ResultSet resu = null;
        try {
            ReOpenConnection(); //lo uso para abrir la conexion
            cst = con.prepareCall("{ call dbo.selectPeliAlquiladasPP(?) }");
            cst.setString(1, id_Peli);
            resu = cst.executeQuery();

        } catch (Exception ex) {
            SM.lastError = ex.getMessage();
        }
        return resu;
    }
    ResultSet getPeliAlqui(String id_Usua,String id_Peli) {
        ResultSet resu = null;
        try {
            ReOpenConnection(); //lo uso para abrir la conexion
            cst = con.prepareCall("{ call dbo.selectPeliAlquiladasPUP(?,?) }");
            cst.setString(1, id_Usua);
            cst.setString(2, id_Peli);
            resu = cst.executeQuery();

        } catch (Exception ex) {
            SM.lastError = ex.getMessage();
        }
        return resu;
    }
}
